Imports System.Data

Public Class Controller
    Dim MQConnection As System.Data.OleDb.OleDbConnection
    Public SupplierTable As DataTable
    Public WorkTable As DataTable
    Public TierTable As DataTable
    Public CandTable As DataTable
    Sub New()

        Dim DataAdapter As OleDb.OleDbDataAdapter
        SupplierTable = New DataTable()

        'connect to the suppliers database
        MQConnection = New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = ..\..\Database\MQ.mdb")
        MQConnection.Open()

        'populate the suppliers dataset with data
        DataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM [Suppliers]", MQConnection)
        DataAdapter.Fill(SupplierTable)
        MQConnection.Close()

        'Construct the temporary tables
        InitializeTempTables()

    End Sub
    Private Function InitializeTempTables() As Boolean
        'Builds the temp tables and populates Q/C ratio for suppliers
        Dim Row As DataRow

        'This table stores the suppliers that have been allocated
        WorkTable = New DataTable("Working")
        WorkTable.Columns.Add(New DataColumn("Sup_ID", System.Type.GetType("System.Int32")))
        WorkTable.Columns.Add(New DataColumn("Tier_No", System.Type.GetType("System.Int32")))
        WorkTable.Columns.Add(New DataColumn("Sup_Q", System.Type.GetType("System.Single")))
        WorkTable.Columns.Add(New DataColumn("Sup_Cap", System.Type.GetType("System.Int32")))
        WorkTable.Columns.Add(New DataColumn("Sup_Cost", System.Type.GetType("System.Single")))
        WorkTable.Columns.Add(New DataColumn("q_c_ratio", System.Type.GetType("System.Single")))

        'This table stores the statistics for tiers
        TierTable = New DataTable("Tier")
        TierTable.Columns.Add(New DataColumn("Tier_No", System.Type.GetType("System.Int32")))
        TierTable.Columns.Add(New DataColumn("Tier_Quality", System.Type.GetType("System.Single")))
        TierTable.Columns.Add(New DataColumn("Tier_Cost", System.Type.GetType("System.Single")))

        'This table stores potential candidates for exchange during a cost constrained calculation
        CandTable = New DataTable("Candidate")
        CandTable.Columns.Add(New DataColumn("Old_ID", System.Type.GetType("System.Int32")))
        CandTable.Columns.Add(New DataColumn("Old_Cap", System.Type.GetType("System.Int32")))
        CandTable.Columns.Add(New DataColumn("New_ID", System.Type.GetType("System.Int32")))
        CandTable.Columns.Add(New DataColumn("Tier_No", System.Type.GetType("System.Int32")))
        CandTable.Columns.Add(New DataColumn("QC_Delta", System.Type.GetType("System.Single")))

        'Calculate the Q/C ratio for each supplier
        For Each Row In SupplierTable.Rows
            Row("q_c_ratio") = (Row("Sup_Q") / Row("Sup_Cost"))
        Next

        Return True

    End Function
    Private Function IsSupplierWorking(ByVal SupplierID As Long) As Boolean
        'Is the specified supplier already in the working table?

        Dim View As DataView = New DataView(WorkTable)
        View.RowFilter = "[Sup_ID] = " & SupplierID.ToString()

        If View.Count > 0 Then
            Return True
        Else
            Return False
        End If

    End Function
    Private Function SupplierRemainingCap(ByVal SupplierID As Long) As Long
        'Returns the remaining capacity for a supplier
        Dim SupMaxCap As Long = 0
        Dim SupUseCap As Long = 0
        Dim View As DataView

        'determine the amount of capacity that has already been allocated for a supplier
        View = New DataView(WorkTable)
        View.RowFilter = "[Sup_ID] = '" & SupplierID.ToString() & "'"
        If View.Count > 0 Then
            SupUseCap = View.Item(0).Item("Sup_Cap")
        Else
            SupUseCap = 0
        End If

        'determine the maximum capacity for a supplier
        View = New DataView(SupplierTable)
        View.RowFilter = "[Sup_ID] = '" & SupplierID.ToString() & "'"
        If View.Count > 0 Then
            SupMaxCap = View.Item(0).Item("Sup_Cap")
        Else
            SupMaxCap = 0
        End If

        If SupMaxCap <= SupUseCap Then
            Return 0
        Else
            Return (SupMaxCap - SupUseCap)
        End If

    End Function
    Private Function AllocateSupplier(ByVal SupplierID As Long, ByVal Demand As Long) As Boolean
        'Add a supplier to the "Working" table

        'retreive the specified supplier record
        Dim View As DataView = New DataView(SupplierTable)
        Dim Row As DataRow
        View.RowFilter = "[Sup_ID] = '" & SupplierID.ToString() & "'"

        If SupplierRemainingCap(SupplierID) >= Demand Then      'confirm that the supplier has not already been fully allocated


            If IsSupplierWorking(SupplierID) Then
                'A. If the supplier is already working, then add to his allocation
                For Each Row In WorkTable.Rows
                    If Row.Item("Sup_ID") = SupplierID Then
                        Row.Item("Sup_Cap") += Demand
                    End If
                Next
            Else
                'B. Otherwise, add the supplier to the working table
                Row = WorkTable.NewRow()
                Row("Sup_ID") = View.Item(0).Item("Sup_ID")
                Row("Tier_No") = View.Item(0).Item("Tier_No")
                Row("Sup_Q") = View.Item(0).Item("Sup_Q")
                'NOTE: the capacity copied into the [Working] table is NOT the supplier's maximum capacity, but is the REQUIRED capacity, which must be smaller or equal
                Row("Sup_Cap") = Demand
                Row("Sup_Cost") = View.Item(0).Item("Sup_Cost")
                Row("q_c_ratio") = (View.Item(0).Item("Sup_Q") / View.Item(0).Item("Sup_Cost"))
                WorkTable.Rows.Add(Row)
            End If
        End If

        Return True

    End Function
    Private Function DeallocateSupplier(ByVal SupplierID As Long, Optional ByVal Amount As Long = 0) As Boolean
        'Remove a supplier from the "Working" table
        Dim Index As Long

        'Find the record
        For Index = 0 To (WorkTable.Rows.Count - 1)
            If WorkTable.Rows(Index).Item("Sup_ID") = SupplierID Then
                If (Amount <> 0) And (Amount < WorkTable.Rows(Index).Item("Sup_Cap")) Then
                    'If an amount was specified, and is less than the amount being used, remove it
                    WorkTable.Rows(Index).Item("Sup_Cap") = WorkTable.Rows(Index).Item("Sup_Cap") - Amount
                Else
                    'Otherwise, completely remove the supplier
                    WorkTable.Rows(Index).Delete()
                    Exit For
                End If
            End If
        Next
        Return True

    End Function
    Private Function SwapSuppliers(ByVal OldSupplierID As Long, ByVal NewSupplierID As Long, Optional ByVal Amount As Long = 0) As Boolean
        'Remove the "old" supplier and replace with the "new" supplier

        Dim x As Boolean
        Dim AmountToTransfer As Long

        'retreive the specified working record
        Dim WorkView As DataView = New DataView(WorkTable)
        WorkView.RowFilter = "[Sup_ID] = '" & OldSupplierID.ToString() & "'"

        If (Amount = 0) Or (Amount > WorkView.Item(0).Item("Sup_Cap")) Then
            AmountToTransfer = WorkView.Item(0).Item("Sup_Cap")
        Else
            AmountToTransfer = Amount
        End If


        'retreive the specified supplier record
        Dim SupView As DataView = New DataView(SupplierTable)
        SupView.RowFilter = "[Sup_ID] = '" & NewSupplierID.ToString() & "'"

        If IsSupplierWorking(OldSupplierID) Then                               'confirm that the supplier is allocated
            If SupplierRemainingCap(NewSupplierID) >= AmountToTransfer Then        'confirm that the "new" supplier can fill the same level of demand
                x = DeallocateSupplier(OldSupplierID, AmountToTransfer)
                x = AllocateSupplier(NewSupplierID, AmountToTransfer)
            Else
                Return False
            End If
        End If

        Return True

    End Function
    Private Function GetHighestTier() As Long
        'Determine the highest tier in the database

        Dim View As DataView = New DataView(SupplierTable)
        View.Sort = "Tier_No DESC"

        Return View.Item(0).Item("Tier_No")

    End Function
    Private Function GetMaxCapacityForTier(ByVal Tier As Long) As Long
        'Determine the maximum demand that can be fulfilled for a specified tier

        Dim Capacity As Long = 0
        Dim Row As DataRow

        For Each Row In SupplierTable.Rows
            If Row("Tier_No") = Tier Then
                Capacity += Row("Sup_Cap")
            End If
        Next

        Return Capacity

    End Function
    Private Function CheckDemand(ByVal Demand As Long) As Boolean
        'Confirm that the required demand can be met by each tier

        Dim Index As Long

        For Index = 1 To GetHighestTier()
            If GetMaxCapacityForTier(Index) < Demand Then
                Return False
            End If
        Next

        Return True

    End Function
    Private Sub CalculateTierStats(ByVal Tier As Long, ByVal Demand As Long)
        Dim View As DataView = New DataView(WorkTable)
        Dim Index As Long = 0
        Dim TierQuality As Single = 0.0
        Dim TierCost As Single = 0.0
        Dim RowUpdated As Boolean = False
        Dim Row As DataRow

        'Calculate stats for the tier based on the current "Working" table
        View.RowFilter = "[Tier_No] = " & Tier.ToString()
        For Index = 0 To (View.Count - 1)
            TierQuality += (View.Item(Index).Item("Sup_Q") * (View.Item(Index).Item("Sup_Cap") / Demand))
            TierCost += (View.Item(Index).Item("Sup_Cost") * (View.Item(Index).Item("Sup_Cap") / Demand))
        Next

        'Record the stats for the tier
        For Each Row In TierTable.Rows
            If Row("Tier_No") = Tier Then
                Row("Tier_Quality") = TierQuality
                Row("Tier_Cost") = TierCost
                RowUpdated = True
            End If
        Next

        If Not RowUpdated Then
            Row = TierTable.NewRow()
            Row("Tier_No") = Tier
            Row("Tier_Quality") = TierQuality
            Row("Tier_Cost") = TierCost
            TierTable.Rows.Add(Row)
        End If

    End Sub
    Private Sub CalculateMaxQualityForTier(ByVal Tier As Long, ByVal Demand As Long)
        'Determine the maximum possible quality value for a specified tier

        Dim x As Boolean
        Dim RemainingDemand As Long = Demand
        Dim Index As Long = 0
        Dim View As DataView = New DataView(SupplierTable)
        Dim CurrentSupplierID As Long = 0
        Dim CurrentSupplierCapacity As Long = 0
        Dim CurrentSupplierQuality As Single = 0.0
        Dim CurrentSupplierCost As Single = 0.0
        Dim TierCost As Single = 0.0
        Dim TierQuality As Single = 0.0

        View.RowFilter = "[Tier_No] = " & Tier.ToString()
        View.Sort = "[Sup_Q] DESC"

        For Index = 0 To (View.Count - 1)

            'When the remaining demand is 0, then the tier is complete
            If RemainingDemand <= 0 Then
                Exit For
            End If

            'Get information about the "current" supplier
            CurrentSupplierID = View.Item(Index).Item("Sup_ID")
            CurrentSupplierCapacity = View.Item(Index).Item("Sup_Cap")
            CurrentSupplierQuality = View.Item(Index).Item("Sup_Q")
            CurrentSupplierCost = View.Item(Index).Item("Sup_Cost")

            'Logic branches based on whether the current supplier can fulfill the entire remaining demand
            If RemainingDemand >= CurrentSupplierCapacity Then
                x = AllocateSupplier(CurrentSupplierID, CurrentSupplierCapacity)
            Else
                x = AllocateSupplier(CurrentSupplierID, RemainingDemand)
            End If

            'Decrement remaining demand
            RemainingDemand -= View.Item(Index).Item("Sup_Cap")
        Next

        'Now that all of the suppliers have been allocated, it's time to calculate stats for the tier
        CalculateTierStats(Tier, Demand)

    End Sub
    Private Function GetCurrentUnitCost(ByVal Demand As Long) As Single
        'Calculates and returns the current cost per unit

        Dim Index As Long = 0
        Dim Index2 As Long = 0
        Dim TotalCost As Single = 0.0

        Dim View As DataView = New DataView(WorkTable)

        For Index = 1 To GetHighestTier()
            View.RowFilter = "[Tier_No] = " & Index.ToString()
            For Index2 = 0 To (View.Count - 1)
                TotalCost += (View.Item(Index2).Item("Sup_Cost") * (View.Item(Index2).Item("Sup_Cap") / Demand))
            Next
        Next

        Return TotalCost

    End Function
    Private Function BuildCandidateList(ByRef CandView As DataView) As Boolean

        Dim WorkRow As DataRow
        Dim CandRow As DataRow
        Dim SupView As DataView = New DataView(SupplierTable)
        Dim Index As Long
        Dim SupID As Long = 0
        Dim SupTier As Long = 0
        Dim SupRemainingCap As Long = 0
        Dim SupQC As Single = 0.0
        Dim FoundCandidate As Boolean = False

        CandTable.Rows.Clear()

        For Each WorkRow In WorkTable.Rows
            'For each working supplier, make a list of suppliers in the same tier to evaluate
            SupView.RowFilter = "[Tier_No] = " & WorkRow("Tier_No").ToString()

            'Search this view for candidates
            For Index = 0 To (SupView.Count - 1)
                SupID = SupView.Item(Index).Item("Sup_ID")
                SupTier = SupView.Item(Index).Item("Tier_No")
                SupRemainingCap = SupplierRemainingCap(SupView.Item(Index).Item("Sup_ID"))
                SupQC = CSng(SupView.Item(Index).Item("q_c_ratio"))

                'Confirm that the potential candidate can match the capacity of the working supplier
                If SupRemainingCap >= WorkRow("Sup_Cap") Then
                    'Confirm that the potential candidate is actually a better value
                    If SupQC > WorkRow("q_c_ratio") Then
                        'If all the conditions are met, create a candidate record
                        CandRow = CandTable.NewRow()
                        CandRow("Old_ID") = WorkRow("Sup_ID")
                        CandRow("Old_Cap") = WorkRow("Sup_Cap")
                        CandRow("New_ID") = SupID
                        CandRow("Tier_No") = WorkRow("Tier_No")
                        CandRow("QC_Delta") = SupQC - WorkRow("q_c_ratio")
                        CandTable.Rows.Add(CandRow)
                        FoundCandidate = True
                    End If
                End If
            Next
        Next

        If FoundCandidate = True Then
            CandView = New DataView(CandTable)
            CandView.Sort = "[QC_Delta] DESC"
            Return True
        Else
            Return False
        End If

    End Function
    Private Function GetPriceSinkPerUnit(ByVal OldSupplierID As Long, ByVal NewSupplierID As Long, ByVal Demand As Long) As Single
        Dim OldSupplierCost As Single = 0.0
        Dim NewSupplierCost As Single = 0.0
        Dim WorkView As DataView = New DataView(WorkTable)
        Dim SupView As DataView = New DataView(SupplierTable)

        WorkView.RowFilter = "[Sup_ID] = " & OldSupplierID
        OldSupplierCost = WorkView.Item(0).Item("Sup_Cost")

        SupView.RowFilter = "[Sup_ID] = " & NewSupplierID
        NewSupplierCost = SupView.Item(0).Item("Sup_Cost")

        Return ((OldSupplierCost - NewSupplierCost) / Demand)

    End Function
    Public Function MaxQualityCalculation(ByVal Demand As Long) As Boolean
        'METHOD: Calculate the maximum possible quality based on the suppliers database

        Dim Index As Long = 0

        If CheckDemand(Demand) Then
            For Index = 1 To GetHighestTier()
                CalculateMaxQualityForTier(Index, Demand)
            Next
            Return True
        Else
            Return False
        End If

    End Function
    Public Function CostConstrainedCalculation(ByVal Demand As Long, ByVal MaxCost As Single) As Boolean
        'METHOD: Calculate the best quality possible while not exceeding the specified maximum cost

        Dim Index As Long = 0
        Dim Result As Boolean = True
        Dim CandView As DataView = New DataView()
        Dim RequiredPriceSink As Single = 0.0
        Dim PriceSinkPerUnit As Single = 0.0
        Dim AmountNeeded As Long = 0
        Dim OldID As Long = 0
        Dim NewID As Long = 0
        Dim OldSup As Long = 0

        If CheckDemand(Demand) Then
            'First, run the basic max quality function
            For Index = 1 To GetHighestTier()
                CalculateMaxQualityForTier(Index, Demand)
            Next

            'Attempt to get under the maximum cost
            Do While (GetCurrentUnitCost(Demand) > MaxCost)
                If BuildCandidateList(CandView) Then
                    OldID = CandView.Item(0).Item("Old_ID")
                    NewID = CandView.Item(0).Item("New_ID")
                    OldSup = CandView.Item(0).Item("Old_Cap")
                    RequiredPriceSink = GetCurrentUnitCost(Demand) - MaxCost
                    PriceSinkPerUnit = GetPriceSinkPerUnit(OldID, NewID, Demand)

                    'If the total amount saved by switching completely to the new supplier is more than the required savings
                    If (PriceSinkPerUnit * OldSup) > RequiredPriceSink Then
                        'swap only what is required
                        AmountNeeded = Math.Ceiling(RequiredPriceSink / PriceSinkPerUnit)
                        SwapSuppliers(OldID, NewID, AmountNeeded)
                    Else
                        'otherwise, swap the suppliers completely
                        SwapSuppliers(OldID, NewID)
                    End If
                Else
                    'If no candidates exist, then the maximum cost cannot be met
                    Return False
                End If
            Loop

            'Succeeded in getting under the maximum cost, recalculate tier stats
            For Index = 1 To GetHighestTier()
                CalculateTierStats(Index, Demand)
            Next

        Else
            'If the demand cannot be met
            Return False
        End If

        Return True

    End Function

End Class
